Bottom Line: Learn how to use the Split by Delimiter into Rows and Columns features of Power Query. Also see how to trim blank spaces and replace values. 

Skill Level: Intermediate

Follow along or practice with the same Excel workbook that I use in the video. Download it here:

Let's say you've just joined a new group (distribution list) and you want to make a list or directory of the members' contact information. The data that you have to start your list is from a group email that you received.

You can take the string of names and emails in the “To” field of your email and quickly turn them into an organized list using Power Query. I'll show you how to turn the data on the right into a table like the one on the left.

The first step in transforming our data is just to copy the entire block, or string, of names/emails. Then paste them into one cell on an Excel worksheet.

With that one cell selected, open up the Power Query Editor. You can do that by clicking on From Table/Range on the Data tab of the Ribbon.

That will bring up the Create Table window. Verify the cell that you are pulling the data from, and that the checkbox for “My table has headers” is NOT checked. When you click OK, the Power Query Editor will open.

The first thing we are going to do in the editor is split our column. On the Home tab of the Ribbon, go to the Split Column menu and choose By Delimiter.

A delimiter is a character, symbol, or space that indicates the beginning or end of a data item. In our case, our email entries are separated from each other by a semicolon. So when the Split Column by Delimiter window appears, select Semicolon from the dropdown menu of delimiters.

In the same window, under Advanced Options, select the option for splitting into Rows. Otherwise each of our email entries will be listed in its own column, which isn't helpful for us.

When you kit OK, your data string will be split so that each row contains the data that was formerly between semicolons.

This is an awesome and somewhat hidden feature of Power Query! And it still works the same if your data table has multiple columns but you are only splitting a single column.

Our next step is to separate the names from the email addresses. What symbol separates these pieces of data? The less than symbol (